import hashlib
import json

from flask import Flask, request
from sqlalchemy import create_engine, Column, Integer, String, Table
from sqlalchemy.orm import sessionmaker, scoped_session, declarative_base

app = Flask(__name__)

# 创建一个引擎，目的是连接到我们的数据库上
engine = create_engine('mysql+pymysql://admin1:123@192.168.1.129:3306/mumushouji',echo=True)
# 打开数据库的连接会话
session = sessionmaker(engine)
# 保证线程安全
db_session = scoped_session(session)
# 获取基类
Base = declarative_base()



class User(Base):
    # 表结构的反射加载
    __table__ = Table("user",Base.metadata,autoload_with=engine)

class Article(Base):
    __table__ = Table("article", Base.metadata, autoload_with=engine)

@app.route("/")
def my_article():
    username = request.args.get("username")
    # all_article = db_session.query(User,Article).join(
    #     Article,User.user_id==Article.user_id).filter(
    #     User.username==username).all()
    # for user,article in all_article:
    #     print(user)
    #     print(user.username)
    #     print(article.title)
    # all_article = db_session.query(User.username,User.nickname, Article.title).join(
    #     Article, User.user_id == Article.user_id).filter(
    #     User.username == username).all()
    # print(all_article)
    # for u,n,a in all_article:
    #     print(u)
    #     print(n)
    #     print(a)
    all_article = db_session.query(User, Article.title).join(
        Article, User.user_id == Article.user_id).filter(
        User.username == username).all()

    for user,title in all_article:
        print(user.username)
        print(title)
    return "ok"

if __name__ == "__main__":
    app.run()
